package net.comm.message.db;

import org.sakaiproject.db.api.iServiceSql;


/**
 * methods for accessing user data in a database.
 */
public class MessageServiceSqlMysql implements iServiceSql
{
	/**
	 * 返回根据主键删除语句
	 */
	public String getDeleteSql()
	{
		return "delete from Messages where seqID=? ";
	}

	/**
	 * 返回根据主键删除语句
	 */
	public String getDelbyIDSql()
	{
		return "delete from Messages where seqID  in (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) and recvDelflg=? " +
				" and sndDelflg=? ";
	}

	/**
	 * 返回插入语句
	 */
	public String getInsertSql()
	{
		return "insert into Messages ( seqID, receiver,  sender,  sendertype, msgID, mtype, title," +
				"  status,  rTime, recvDelflg,  sndDelflg,  sTime,  mTime " +
				" ) values (?,?,?,?,?,?,?,?,?,?,?,?,?)";
	}
	
	/**
	 * 返回根据主键修改语句
	 */
	public String getUpdateSql()
	{
		return "update Messages set Receiver=?, Sender=?, sendertype=?, msgID=?, mtype=?,  title=?,  status=?,  rTime=?, " +
				"recvDelflg=?,  sndDelflg=?,  sTime=?,  mTime=? " +
				"  where seqID=? ";
	}

	/**
	 * 返回根据主键修改语句
	 */
	public String getUpdstsSql()
	{
		return "update Messages set  status=?,  rTime=?, recvDelflg=?,  sndDelflg=? " +
				"  where seqID=? ";
	}

	/**
	 * 返回根据主键修改接收删除标志语句
	 */
	public String getUpdRecvDelFlgSql()
	{
		return "update Messages set recvDelflg=?,  Rtime=? " +
				"  where seqID  in (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";
	}
	/**
	 * 返回根据主键修改发送删除标志语句
	 */
	public String getUpdSndDelFlgSql()
	{
		return "update Messages set sndDelflg=?,  Rtime=? " +
		"  where seqID  in (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";
	}

	/**
	 * 返回根据主键查询语句
	 */
	public String getbykeySql()
	{
		return "select seqID, receiver,  sender,  sendertype, msgID,  mtype, title,  status,  rTime," +
				" recvDelflg,  sndDelflg,  sTime,  mTime " +
				" from Messages where seqID=? ";
	}
	
	/**
	 * 返回根据会员ID查询语句 
	 */
	public String getByUserIDSql()
	{
		StringBuffer sb =  new StringBuffer("select seqID, receiver,  sender,  sendertype, msgID, mtype,  title, " +
				" status,  rTime, recvDelflg,  sndDelflg,  sTime,  mTime " +
				" from Messages");
		
		sb.append(" where Receiver=?  Order by MTime desc ");
				
		return sb.toString();
	}
	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecbyPageSql()
	{
	
		StringBuffer sb =  new StringBuffer("select  seqID, receiver,  sender,  sendertype, msgID, mtype, " +
				" title,  status,  rTime, recvDelflg,  sndDelflg,  sTime,  mTime " +
				" from Messages");
		
		sb.append(" where Receiver=? and Sender=? ");
		sb.append(" limit ?, ?");
				
		return sb.toString();	
	}
	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecReceiverbyPageSql()
	{
	
		StringBuffer sb =  new StringBuffer("select seqID, receiver,  sender,  sendertype, msgID, mtype, " +
				" title,  status,  rTime, recvDelflg,  sndDelflg,  sTime,  mTime " +
				"from Messages");
		
		sb.append(" where receiver=? and recvDelflg=? Order by mTime desc ");
		sb.append(" limit ?, ? ");
				
		return sb.toString();	
	}	
	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecSenderbyPageSql()
	{
	
		StringBuffer sb =  new StringBuffer("select seqID, receiver,  sender,  sendertype, msgID, mtype, " +
				" title,  status,  rTime, recvDelflg,  sndDelflg,  sTime,  mTime " +
				"from Messages ");
		
		sb.append(" where sender=? and sndDelflg=? ");
		sb.append(" limit ?, ?");
				
		return sb.toString();	
	}	

	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecSenderTypebyPageSql()
	{
	
		StringBuffer sb =  new StringBuffer("select seqID, receiver,  sender,  sendertype, msgID, mtype, " +
				" title,  status,  rTime, recvDelflg,  sndDelflg,  sTime,  mTime " +
				"from Messages");
		
		sb.append(" where sendertype=? and recvDelflg=? Group by sender ");
		sb.append(" limit ?, ?");
				
		return sb.toString();	
	}	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecSql()
	{
	
		StringBuffer sb =  new StringBuffer("select seqID, receiver,  sender,  sendertype, msgID, mtype, " +
				" title,  status,  rTime, recvDelflg,  sndDelflg,  sTime,  mTime " +
				"from Messages");
		
		return sb.toString();	
	}
	
	/**
	 * 返回所有记录数
	 */
	public String getCountSql1()
	{
		return "select count(*) as COUNT from Messages";
	}


	/**
	 * 返回根据接收会员查询的记录数
	 */
	public String getCountSql2()
	{
		return "select count(*) as COUNT from Messages where Receiver=? ";
	}

	/**
	 * 返回根据接收会员查询的记录数
	 */
	public String getCountByReceiverSql()
	{
		return "select count(*) as COUNT from Messages where  receiver=? and recvDelflg=? ";
	}

	/**
	 * 返回根据接收会员查询的记录数
	 */
	public String getCountBySenderSql()
	{
		return "select count(*) as COUNT from Messages where  sender=? and sndDelflg=? ";
	}

	/**
	 * 返回根据接收会员查询的记录数
	 */
	public String getCountBysenderTypeSql()
	{
		return "select count(*) as COUNT from Messages where sendertype=? and recvDelflg=? Group by sender ";
	}
	
	/**
	 * 返回根据主键修改接收删除标志语句
	 */
	public String getUnRedNumSql()
	{
		return "select count(*) as COUNT from Messages " +
				"  where Status=? and seqID  in (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";
	}
	
	/**
	 * @see net.comm.job.db.iServiceSql.impl.UserServiceSql#getMaxInputsForSelectWhereInQueries()
	 */
	public int getMaxInputsForSelectWhereInQueries() {
		// For Oracle, the maximum supported number of expressions in a list is 1000. 
		return 1000;
	}
}
